<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Model\Student;
use App\Model\Role;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Storage;

class StudentController extends Controller
{
  public function index(Request $request)
  {
    $class_id = $request->class_id;
    $class_type = $request->class_type;

    $start_begin_time = $request->start_begin_time ?: ''; // 开班时间
    $start_end_time = $request->start_end_time ?: '';

    $sign_begin_time = $request->sign_begin_time ?: ''; //报名时间（注册时间）
    $sign_end_time = $request->sign_end_time ?: '';

    $fee_status = $request->fee_status; // 交费情况
    $status = $request->status; // 状态

    $search = $request->search ?: '';
    $page_size = $request->page_size ?: 10;
    $sort = $request->sort ?: '-id';

    $search = trim($search);

    $query = Student::leftJoin('sf_class', 'sf_class.id', '=', 'sf_student.class_id');
    $role = $request->_admin->role;
    if ($role === 'admin' || $role === 'treasurer') {
      // 财务可以查看收费情况
      $query->select('sf_student.*', 'sf_class.class_type', 'sf_class.class_name', 'sf_class.year', 'sf_class.class_start_time');
    } else {
      $query->select('sf_student.id', 'wx_nickname', 'name', 'sex', 'phone', 'provice', 'city', 'county', 'now_status', 'school', 'major', 'education', 'pre_career', 'is_base', 'other_name', 'other_phone', 'other_relation', 'source', 'sf_student.remarks', 'fee_status', 'sf_student.status', 'employment_unit', 'salary', 'sf_student.created_time', 'sf_class.class_type', 'sf_class.class_name', 'sf_class.year', 'sf_class.class_start_time');
    }

    if ($class_id !== null) {
      $query->where('sf_student.class_id', $class_id);
    }
    if ($class_type !== null) {
      $query->where('class_type', $class_type);
    }
    if ($fee_status !== null) {
      $query->where('fee_status', $fee_status);
    }
    if ($status !== null) {
      $query->where('status', $status);
    }
    if ($start_begin_time !== '') {
      $query->where('class_start_time', '>=', get_timestamp($start_begin_time));
    }
    if ($start_end_time !== '') {
      // 以月单位+1月
      $query->where('class_start_time', '<=', get_timestamp(strtotime('+1 month', strtotime($start_end_time))));
    }
    if ($sign_begin_time !== '') {
      $query->where('sf_student.created_time', '>=', get_timestamp($sign_begin_time));
    }
    if ($sign_end_time !== '') {
      // 以天单位+1天
      $query->where('sf_student.created_time', '<=', get_timestamp($sign_end_time) + 86400 * 1000);
    }
    if ($search !== '') {
      $query->where(function ($query) use ($search) {
        return $query->Orwhere('name', 'like', "%{$search}%")
          ->Orwhere('phone', 'like', "%{$search}%")
          ->Orwhere('wx_nickname', 'like', "%{$search}%");
      });
    }
    if ($sort[0] === '+') {
      $query->orderBy(substr($sort, 1), 'ASC');
    } else {
      $query->orderBy(substr($sort, 1), 'DESC');
    }

    return resJson(self::HTTP_0_SUCCESS, $query->paginate($page_size));
  }

  // 修改学生信息（应收学费和实收学费）
  public function update(Request $request, $id)
  {
    $student = Student::find($id);
    $input = $request->all();
    if (!empty($input['receipts_fee']) && !empty($input['receivable_fee'])) {
      // 实收-应收
      $input['fee_status'] = $input['receipts_fee'] - $input['receivable_fee'] >= 0 ? 1 : 0;
    }
    $input['updated_time'] = millisecond();
    if ($student) {
      $student->fill($input);
      $student->save();
      return resJson(self::HTTP_0_SUCCESS);
    } else {
      return resJson(self::HTTP_500_ERROR);
    }
  }

  public function destroy($id)
  {
    Student::destroy($id);
    return resJson(self::HTTP_0_SUCCESS);
  }

  public function destroyAll(Request $request)
  {
    $a = $request->ids;
    Student::whereIn('id', $request->ids)->delete();
    return resJson(self::HTTP_0_SUCCESS);
  }

  public function download(Request $request)
  {
    $xls_type = $request->xls_type ?: 1; // 1：学生就业信息， 2:学生信息
    $class_type = $request->class_type;
    $class_id = $request->class_id;
    $query = Student::leftJoin('sf_class', 'sf_class.id', '=', 'sf_student.class_id');

    $query->select('sf_student.*', 'sf_class.class_type', 'sf_class.class_name', 'sf_class.year', 'sf_class.class_start_time');
    if ($class_id !== null) {
      $query->where('sf_student.class_id', $class_id);
    }
    if ($class_type !== null) {
      $query->where('class_type', $class_type);
      $query->orderBy('class_id', 'desc');
    }
    $query->orderBy('id', 'desc');
    $students = $query->get();
    if (!$students->isEmpty()) {
      if ($xls_type === 1) {
        $data = $this->studentXls1($students);
      } else {
        $data = $this->studentXls2($students);
      }
      return resJson(self::HTTP_0_SUCCESS, $data);
    } else {
      return resJson(self::HTTP_500_ERROR, '没有数据');
    }
  }

  private function studentXls1($students)
  {
    $students = $students->map(function ($item, $key) {
      return [
        $key + 1,
        $item['name'],
        $item['sex'] == 1 ? '男' : '女',
        $item['provice'] . $item['city'] . $item['county'],
        $item['major'],
        $item['employment_unit'],
        $item['salary'],
        $item['remarks'],
      ];
    });
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('学生信息');
    $sheet->setCellValue('A1', '编号')
      ->setCellValue('B1', '姓名')
      ->setCellValue('C1', '性别')
      ->setCellValue('D1', '籍贯')
      ->setCellValue('E1', '专业')
      ->setCellValue('F1', '就业单位')
      ->setCellValue('G1', '薪资')
      ->setCellValue('H1', '链接');
    $letters = range('A', 'Z');

    $row = 2; // 行
    foreach ($students as $student) {
      foreach ($student as $column => $value) {
        $sheet->setCellValue($letters[$column] . $row, $value);
      }
      $row++;
    }
    $writer = new Xlsx($spreadsheet);
    $fileName =  time() . '.xlsx';
    $path = storage_path('app' . DIRECTORY_SEPARATOR . 'public' . DIRECTORY_SEPARATOR . $fileName);
    $writer->save($path);
    return Storage::disk('public')->url($fileName);
    // return '/storage/' . $fileName;
  }

  private function studentXls2($students)
  {
    $students = $students->map(function ($item, $key) {
      return [
        $key + 1,
        $item['class_type'] == 1 ? 'UI' : '前端',
        $item['year'],
        $item['class_name'],
        $item['name'],
        $item['sex'] == 1 ? '男' : '女',
        $item['receipts_fee'],
        $item['receivable_fee'] - $item['receipts_fee'],
        $item['remarks'],
      ];
    });
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('学生信息');
    $sheet->setCellValue('A1', '编号')
      ->setCellValue('B1', '类型')
      ->setCellValue('C1', '年份')
      ->setCellValue('D1', '班级')
      ->setCellValue('E1', '姓名')
      ->setCellValue('F1', '性别')
      ->setCellValue('G1', '实收学费')
      ->setCellValue('H1', '欠费')
      ->setCellValue('I1', '备注');
    $letters = range('A', 'Z');

    $row = 2; // 行
    foreach ($students as $student) {
      foreach ($student as $column => $value) {
        $sheet->setCellValue($letters[$column] . $row, $value);
      }
      $row++;
    }
    $writer = new Xlsx($spreadsheet);
    $fileName =  time() . '.xlsx';
    $path = storage_path('app/public/' . $fileName);
    $writer->save($path);
    return Storage::disk('public')->url($fileName);
  }
}
